ALTER PROCEDURE sp_save_process_specification_file_product_info
(
	@isSelected			INT,
	@specFile_Id		INT,
	@processStepId		INT,
	@effectDate			DATETIME,
	@createdBy			VARCHAR(50),
	@isDefault			INT,
	@VENDOR_IDS			VARCHAR(800)
)
AS

BEGIN
	DECLARE @specFileIdTemp INT
	DECLARE @processStepIdTemp  INT
	DECLARE @effectDateTemp VARCHAR(50)
	DECLARE @createdByTemp VARCHAR(50)
	DECLARE @isDefaultTemp INT
	/*
	--If Part Number Selected
	IF ( @isSelected = 0 ) 
	BEGIN

		IF ( ( SELECT COUNT(*) FROM Product_Spec_table WHERE ProcessStep_Id = @processStepId AND specFile_Id <> @specFile_Id ) > 0 )
		BEGIN
			SELECT @specFileIdTemp = SpecFile_Id, @processStepIdTemp = ProcessStep_Id, @effectDateTemp = effect_date, @createdByTemp = created_BY FROM Product_Spec_table WHERE ProcessStep_Id = @processStepId
			INSERT INTO Product_Spec_History_Table ( SpecFile_Id, ProcessStep_Id, effect_date, End_date, created_By )VALUES ( @specFileIdTemp, @processStepIdTemp, @effectDateTemp, CURRENT_TIMESTAMP, @createdByTemp )
			DELETE FROM Product_Spec_table  WHERE ProcessStep_Id = @processStepId AND specFile_Id <> @specFile_Id
			INSERT INTO Product_Spec_table ( SpecFile_Id, ProcessStep_Id, effect_date, created_By )VALUES ( @specFile_Id, @processStepId, @effectDate, @createdBy )		
		END
		ELSE
		BEGIN
			IF ( ( SELECT COUNT(*) FROM Product_Spec_table WHERE ProcessStep_Id = @processStepId AND specFile_Id = @specFile_Id ) = 0 )
			BEGIN
				INSERT INTO Product_Spec_table ( SpecFile_Id, ProcessStep_Id, effect_date, created_By )VALUES ( @specFile_Id, @processStepId, @effectDate, @createdBy )		
			END
		END

	END
	ELSE
	--If Part Number Not Selected
	BEGIN
		IF ( ( SELECT COUNT(*) FROM Product_Spec_table WHERE ProcessStep_Id = @processStepId AND specFile_Id = @specFile_Id ) <> 0 )
		BEGIN
			SELECT @specFileIdTemp = SpecFile_Id, @processStepIdTemp = ProcessStep_Id, @effectDateTemp = effect_date, @createdByTemp = created_BY FROM Product_Spec_table WHERE ProcessStep_Id = @processStepId
			INSERT INTO Product_Spec_History_Table ( SpecFile_Id, ProcessStep_Id, effect_date, End_date, created_By )VALUES ( @specFileIdTemp, @processStepIdTemp, @effectDateTemp, CURRENT_TIMESTAMP, @createdByTemp )
			DELETE FROM Product_Spec_table  WHERE ProcessStep_Id = @processStepId AND specFile_Id = @specFile_Id
		END
	END*/

	--NEW CODE--NEW CODE--NEW CODE--NEW CODE--NEW CODE--NEW CODE
	IF ( @isSelected = 0 )
	BEGIN
		SET @VENDOR_IDS = ISNULL(@VENDOR_IDS,'0')
		DELETE FROM PRODUCT_SPEC_TABLE WHERE ProcessStep_Id = @processStepId AND specFile_Id = @specFile_Id
		INSERT INTO PRODUCT_SPEC_TABLE ( SpecFile_Id, ProcessStep_Id, effect_date, created_By, VENDOR_ID  )
			SELECT @specFile_Id, @processStepId, @effectDate, @createdBy, PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING(@VENDOR_IDS)
		
		/*IF ( ( SELECT COUNT(*) FROM Product_Spec_table WHERE ProcessStep_Id = @processStepId AND specFile_Id = @specFile_Id AND VENDOR_ID = @VENDOR_ID) = 0 )
		BEGIN
			INSERT INTO Product_Spec_table ( SpecFile_Id, ProcessStep_Id, effect_date, created_By, VENDOR_ID ) VALUES ( @specFile_Id, @processStepId, @effectDate, @createdBy, @VENDOR_ID )
		END*/

		IF( ( SELECT COUNT(*) FROM Product_Spec_table WHERE ProcessStep_Id = @processStepId AND specFile_Id = @specFile_Id ) > 0 )
		BEGIN
			IF( @isDefault = 0 )
			BEGIN
				UPDATE Product_Spec_table SET Is_Default = 1 WHERE ProcessStep_Id = @processStepId AND specFile_Id <> @specFile_Id
			END
			UPDATE Product_Spec_table SET Is_Default = @isDefault WHERE ProcessStep_Id = @processStepId AND specFile_Id = @specFile_Id
		END
	END
	ELSE
	BEGIN--If Part Number Not Selected
		IF ( ( SELECT COUNT(*) FROM Product_Spec_table WHERE ProcessStep_Id = @processStepId AND specFile_Id = @specFile_Id ) <> 0 )
		BEGIN
			--SELECT @specFileIdTemp = SpecFile_Id, @processStepIdTemp = ProcessStep_Id, @effectDateTemp = effect_date, @createdByTemp = created_BY, @isDefaultTemp = Is_Default FROM Product_Spec_table WHERE ProcessStep_Id = @processStepId
			INSERT INTO Product_Spec_History_Table ( SpecFile_Id, ProcessStep_Id, effect_date, End_date, created_By, Is_Default, VENDOR_ID) --VALUES ( @specFileIdTemp, @processStepIdTemp, @effectDateTemp, CURRENT_TIMESTAMP, @createdByTemp, @isDefaultTemp )
				SELECT SpecFile_Id, ProcessStep_Id, effect_date, CURRENT_TIMESTAMP, created_BY, Is_Default, VENDOR_ID FROM Product_Spec_table WHERE ProcessStep_Id = @processStepId AND specFile_Id = @specFile_Id
			DELETE FROM Product_Spec_table  WHERE ProcessStep_Id = @processStepId AND specFile_Id = @specFile_Id
		END
	END
END

